package hevs.eServices.server;

import hevs.eServices.client.MySQLQueryService;
import hevs.eServices.client.object.Data;
import hevs.eServices.client.object.Item;
import hevs.eServices.client.object.Meter;
import hevs.eServices.client.object.Property;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

@SuppressWarnings("serial")
public class MySQLQueryServiceImpl extends RemoteServiceServlet implements MySQLQueryService {

	Connection conn;

	public MySQLQueryServiceImpl() {
		this.conn = MySQLConnection.getInstance().getConn();
	}
	
	@Override
	public ArrayList<Data> testQuery(String input) throws IllegalArgumentException {
		
		ArrayList<Data> tempDataList = new ArrayList<Data>();
		
		try {
			PreparedStatement ps = conn.prepareStatement(input);
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				Data tempData = new Data(result.getInt("value"));
				
				tempDataList.add(tempData);
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempDataList;
	}

	@Override
	public ArrayList<String> getDateLimit() throws IllegalArgumentException {

		ArrayList<String> tempDateList = new ArrayList<String>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT MIN(DATE_FORMAT(t_data.date, '%Y-%m-%d')) 'start_date', " +
														 "MAX(DATE_FORMAT(t_data.date, '%Y-%m-%d')) 'end_date' " +
														 "FROM t_data");
			
			ResultSet result = ps.executeQuery();

			result.next();
			
			tempDateList.add(result.getString("start_date"));
			tempDateList.add(result.getString("end_date"));
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		} 
		
		return tempDateList;
	}
	
	@Override
	public ArrayList<String> getDateLimit(int idMeter) throws IllegalArgumentException {
		
		ArrayList<String> tempDateList = new ArrayList<String>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT MIN(DATE_FORMAT(t_data.date, '%Y-%m-%d')) 'start_date', " +
														 "MAX(DATE_FORMAT(t_data.date, '%Y-%m-%d')) 'end_date' " +
														 "FROM t_data " +
														 "WHERE id_t_meter = ?");
			
			ps.setInt(1, idMeter);
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();

			result.next();
			
			tempDateList.add(result.getString("start_date"));
			tempDateList.add(result.getString("end_date"));
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		} 
		
		return tempDateList;
	}

	@Override
	public int getDaySum(String startDate, String endDate) throws IllegalArgumentException {

		int days = 0;
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT DATEDIFF(?, ?) 'days'");
			
			ps.setString(1, startDate);
			ps.setString(2, endDate);
			
			ResultSet result = ps.executeQuery();
			
			result.next();
			
			days = result.getInt("days");
			
			if(days < 0) {
				days = days * -1;
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return days;
	}
	
	@Override
	public Item getItem(int idItem) throws IllegalArgumentException {

		Item tempItem = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_object, name " +
														 "FROM t_object " +
														 "WHERE id_t_object = ?");
			
			ps.setInt(1, idItem);
			
			System.out.println("getItem=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			result.next();
			
			tempItem = new Item(result.getInt("id_t_object"),
								result.getString("name"));
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempItem;
	}
	
	@Override
	public Item getFullItem(int idItem) throws IllegalArgumentException {

		Item tempItem = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_object, name " +
														 "FROM t_object " +
														 "WHERE id_t_object = ?");
			
			ps.setInt(1, idItem);
			
			ResultSet result = ps.executeQuery();
			
			result.next();
			
			tempItem = new Item(result.getInt("id_t_object"),
								result.getString("name"));
			
			tempItem.setListMeter(getFullListMeter(tempItem.getId()));
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempItem;
	}
	
	@Override
	public Meter getMeter(int idMeter) throws IllegalArgumentException {
		
		Meter tempMeter = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_meter, id_t_object, name, manufacturer, model " +
														 "FROM t_meter, t_fluid, t_meter_type " +
														 "WHERE t_meter.id_t_fluid = t_fluid.id_t_fluid " +
														 "AND t_meter.id_t_meter_type = t_meter_type.id_t_meter_type " +
														 "AND id_t_meter = ?");

			ps.setInt(1, idMeter);
			
			ResultSet result = ps.executeQuery();
			
			result.next();
			
			tempMeter = new Meter(result.getInt("id_t_meter"),
								  result.getString("name"),
								  result.getString("manufacturer") + " " + result.getString("model"));
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempMeter;
	}

	@Override
	public Meter getFullMeter(int idMeter) throws IllegalArgumentException {

		Meter tempMeter = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_meter, id_t_object, name, manufacturer, model " +
														 "FROM t_meter, t_fluid, t_meter_type " +
														 "WHERE t_meter.id_t_fluid = t_fluid.id_t_fluid " +
														 "AND t_meter.id_t_meter_type = t_meter_type.id_t_meter_type " +
														 "AND id_t_meter = ?");

			ps.setInt(1, idMeter);
			
			ResultSet result = ps.executeQuery();
			
			result.next();
			
			tempMeter = new Meter(result.getInt("id_t_meter"),
								  result.getString("name"),
								  result.getString("manufacturer") + " " + result.getString("model"));

			tempMeter.setItem(getItem(result.getInt("id_t_object")));
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempMeter;
	}
	
	@Override
	public ArrayList<Item> getListItem() throws IllegalArgumentException {

		ArrayList<Item> tempItemList = new ArrayList<Item>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_object, name " +
					 									 "FROM t_object ");
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				tempItemList.add(new Item(result.getInt("id_t_object"),
										  result.getString("name")));
			}
			
			System.out.println("results=" + tempItemList.size() + "rows");
			
			for(Item i : tempItemList) {
				System.out.println("item:id=" + i.getId() + ",name=" + i.getName());
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempItemList;
	}

	@Override
	public ArrayList<Item> getFullListItem() throws IllegalArgumentException {

		ArrayList<Item> tempItemList = new ArrayList<Item>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_object, name " +
					 									 "FROM t_object ");
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				Item tempItem = new Item(result.getInt("id_t_object"),
						  				 result.getString("name"));
				
				tempItem.setListMeter(getFullListMeter(tempItem.getId()));
				
				if(!tempItem.getListMeter().isEmpty())
					tempItemList.add(tempItem);
			}
			
			System.out.println("results=" + tempItemList.size() + "rows");
			
			for(Item i : tempItemList) {
				System.out.println("item:id=" + i.getId() + ",name=" + i.getName());
				for(Meter m : i.getListMeter()) {
					System.out.println("meter:id=" + m.getId() + ",meterType=" + m.getMeterType() + ",fluid=" + m.getFluid());
					for(Property p : m.getListProperty()) {
						System.out.println("property:id=" + p.getId() + ",name=" + p.getName() + ",value=" + p.getValue());
					}
				}
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempItemList;
		
	}

	@Override
	public ArrayList<Meter> getListMeter(int idItem) throws IllegalArgumentException {

		ArrayList<Meter> tempMeterList = new ArrayList<Meter>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_meter, name, manufacturer, model " +
														 "FROM t_meter, t_fluid, t_meter_type " +
														 "WHERE t_meter.id_t_fluid = t_fluid.id_t_fluid " +
														 "AND t_meter.id_t_meter_type = t_meter_type.id_t_meter_type " +
														 "AND id_t_object = ?");
			
			ps.setInt(1, idItem);
			
			ResultSet result = ps.executeQuery();
			
			while (result.next()) {
				Meter tempMeter = new Meter(result.getInt("id_t_meter"),
											result.getString("name"),
											result.getString("manufacturer") + " " + result.getString("model"));
				
				tempMeterList.add(tempMeter);
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempMeterList;
	}
	
	@Override
	public ArrayList<Meter> getFullListMeter(int idItem) throws IllegalArgumentException {

		ArrayList<Meter> tempMeterList = new ArrayList<Meter>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT id_t_meter, name, manufacturer, model " +
														 "FROM t_meter, t_fluid, t_meter_type " +
														 "WHERE t_meter.id_t_fluid = t_fluid.id_t_fluid " +
														 "AND t_meter.id_t_meter_type = t_meter_type.id_t_meter_type " +
														 "AND id_t_object = ?");
			
			ps.setInt(1, idItem);
			
			ResultSet result = ps.executeQuery();
			
			Item tempItem = getItem(idItem);
			
			while (result.next()) {
				Meter tempMeter = new Meter(result.getInt("id_t_meter"),
											result.getString("name"),
											result.getString("manufacturer") + " " + result.getString("model"));
				
				tempMeter.setListProperty(getFullListProperty(tempMeter.getId()));
				
				tempItem.addMeter(tempMeter);
				
				tempMeterList.add(tempMeter);
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempMeterList;
	}

	@Override
	public ArrayList<Property> getListProperty(int idMeter) throws IllegalArgumentException {

		ArrayList<Property> tempPropertyList = new ArrayList<Property>();

		try {
			PreparedStatement ps = conn.prepareStatement("SELECT t_property.id_t_property, name, description, value " +
														 "FROM tj_meter_property, t_property " +
														 "WHERE tj_meter_property.id_t_property = t_property.id_t_property " +
														 "AND id_t_meter = ?");
			
			ps.setInt(1, idMeter);
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				Property tempProperty = new Property(result.getInt("id_t_property"),
													 result.getString("name"),
													 result.getString("description"),
													 result.getString("value"));
				
				tempPropertyList.add(tempProperty);
			}
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempPropertyList;
	}

	@Override
	public ArrayList<Property> getFullListProperty(int idMeter) throws IllegalArgumentException {
		
		ArrayList<Property> tempPropertyList = new ArrayList<Property>();

		try {
			PreparedStatement ps = conn.prepareStatement("SELECT t_property.id_t_property, name, description, value " +
														 "FROM tj_meter_property, t_property " +
														 "WHERE tj_meter_property.id_t_property = t_property.id_t_property " +
														 "AND id_t_meter = ?");
			
			ps.setInt(1, idMeter);
			
			ResultSet result = ps.executeQuery();
			
			Meter tempMeter = getFullMeter(idMeter);
			
			while(result.next()) {
				Property tempProperty = new Property(result.getInt("id_t_property"),
													 result.getString("name"),
													 result.getString("description"),
													 result.getString("value"));
				
				tempProperty.setMeter(tempMeter);
				
				tempPropertyList.add(tempProperty);
			}
			
			tempMeter.setListProperty(tempPropertyList);
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempPropertyList;
	}

	@Override
	public ArrayList<Data> getListDataByHour(int idMeter, String startDate, String endDate) throws IllegalArgumentException {
		
		ArrayList<Data> tempDataList = new ArrayList<Data>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT DATE_FORMAT(date, '%Y-%m-%d %H') 'date', t_parameter.name 'parameter', t_unit.name 'unit', MIN(value) 'first_value', MAX(value) 'last_value', suffix " +
														 "FROM t_data, t_unit, t_parameter " +
														 "WHERE t_data.id_t_unit = t_unit.id_t_unit " +
														 "AND t_data.id_t_parameter = t_parameter.id_t_parameter " +
														 "AND id_t_meter = ? " +
														 "AND date BETWEEN ? AND ? " +
														 "GROUP BY DATE_FORMAT(date, '%Y-%m-%d %H'), t_parameter.name");
			
			ps.setInt(1, idMeter);
			ps.setString(2, startDate);
			ps.setString(3, endDate + " 23:59:59");
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				tempDataList.add(new Data(result.getString("suffix"),
										  result.getString("unit"), 
										  result.getString("parameter"),
										  result.getDouble("first_value"),
										  result.getDouble("last_value"),
										  result.getString("date")));
			}
			
			System.out.println("results=" + tempDataList.size() + "rows");
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempDataList;
	}

	@Override
	public ArrayList<Data> getListDataByDay(int idMeter, String startDate, String endDate) throws IllegalArgumentException {
		
		ArrayList<Data> tempDataList = new ArrayList<Data>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT DATE_FORMAT(date, '%Y-%m-%d') 'date', t_parameter.name 'parameter', t_unit.name 'unit', MIN(value) 'first_value', MAX(value) 'last_value', suffix " +
														 "FROM t_data, t_unit, t_parameter " +
														 "WHERE t_data.id_t_unit = t_unit.id_t_unit " +
														 "AND t_data.id_t_parameter = t_parameter.id_t_parameter " +
														 "AND id_t_meter = ? " +
														 "AND date BETWEEN ? AND ? " +
														 "GROUP BY DATE_FORMAT(date, '%Y-%m-%d'), t_parameter.name");
			
			ps.setInt(1, idMeter);
			ps.setString(2, startDate);
			ps.setString(3, endDate + " 23:59:59");
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				tempDataList.add(new Data(result.getString("suffix"),
										  result.getString("unit"), 
										  result.getString("parameter"),
										  result.getDouble("first_value"),
										  result.getDouble("last_value"),
										  result.getString("date")));
			}
			
			System.out.println("results=" + tempDataList.size() + "rows");
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempDataList;
	}
	
	@Override
	public ArrayList<Data> getListDataByWeek(int idMeter, String startDate, String endDate) throws IllegalArgumentException {
		
		ArrayList<Data> tempDataList = new ArrayList<Data>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT DATE_FORMAT(date, '%Y-%m-%d %u') 'date', t_parameter.name 'parameter', t_unit.name 'unit', MIN(value) 'first_value', MAX(value) 'last_value', suffix " +
														 "FROM t_data, t_unit, t_parameter " +
														 "WHERE t_data.id_t_unit = t_unit.id_t_unit " +
														 "AND t_data.id_t_parameter = t_parameter.id_t_parameter " +
														 "AND id_t_meter = ? " +
														 "AND date BETWEEN ? AND ? " +
														 "GROUP BY DATE_FORMAT(date, '%u'), t_parameter.name " +
														 "ORDER BY DATE_FORMAT(date, '%Y-%m-%d')");
			
			ps.setInt(1, idMeter);
			ps.setString(2, startDate);
			ps.setString(3, endDate + " 23:59:59");
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				
				tempDataList.add(new Data(result.getString("suffix"),
										  result.getString("unit"), 
										  result.getString("parameter"),
										  result.getDouble("first_value"),
										  result.getDouble("last_value"),
										  result.getString("date")));
			}
			
			System.out.println("results=" + tempDataList.size() + "rows");
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempDataList;
	}
	
	@Override
	public ArrayList<Data> getListDataByMonth(int idMeter, String startDate, String endDate) throws IllegalArgumentException {
		
		ArrayList<Data> tempDataList = new ArrayList<Data>();
		
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT LAST_DAY(date) 'date', t_parameter.name 'parameter', t_unit.name 'unit', MIN(value) 'first_value', MAX(value) 'last_value', suffix " +
														 "FROM t_data, t_unit, t_parameter " +
														 "WHERE t_data.id_t_unit = t_unit.id_t_unit " +
														 "AND t_data.id_t_parameter = t_parameter.id_t_parameter " +
														 "AND id_t_meter = ? " +
														 "AND date BETWEEN ? AND ? " +
														 "GROUP BY DATE_FORMAT(date, '%Y-%m'), t_parameter.name");
			
			ps.setInt(1, idMeter);
			ps.setString(2, startDate);
			ps.setString(3, endDate + " 23:59:59");
			
			System.out.println("query=" + ps.toString());
			
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				tempDataList.add(new Data(result.getString("suffix"),
										  result.getString("unit"), 
										  result.getString("parameter"),
										  result.getDouble("first_value"),
										  result.getDouble("last_value"),
										  result.getString("date")));
			}
			
			System.out.println("results=" + tempDataList.size() + "rows");
			
			result.close();
			ps.close();
			
		} catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
		}
		
		return tempDataList;
	}
	
}
